import datetime
import math
import os
import sys
import time
import traceback

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.ProdDb import ProdDb
from src.db.FCDB import FCDB
from src.utils import CommonUtils, BatchUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger
from elasticsearch.helpers import bulk
from itertools import groupby
from operator import itemgetter

from elasticsearch import Elasticsearch
from tqdm import tqdm

from src.base import Consts
from src.db.AbsSqlServer import AbsSqlServerDev

es = Elasticsearch(
    [{'host': Consts.elasticsearch_url, 'port': Consts.elasticsearch_port}], timeout=3600
)

logger = Logger()


class SyncSecuritiesToEs:
    """
    计算债券相关的数据
    """

    def __init__(self, logger, localDb, fcdb, absSqlServer):
        self.logger = logger
        self.fcdb = fcdb
        self.localDb = localDb
        self.size = 10000
        self.absSqlServer = absSqlServer
        self.arg = sys.argv

    def __queryBpData(self):
        sql_platform_bp = """
               select security_id ,interest_rate_spread,termtomaturity  from base_bonds_interest_rate_spread_platform  
               where trade_date =(select max(trade_date) from base_bonds_interest_rate_spread_platform)
        """
        sql_industry_bp = """
               select security_id ,interest_rate_spread,termtomaturity  from base_bonds_interest_rate_spread_industry
               where trade_date =(select max(trade_date) from base_bonds_interest_rate_spread_industry)
            """

        industry_bp_result = self.absSqlServer.execute_query(sql_industry_bp)
        platform_bp_result = self.absSqlServer.execute_query(sql_platform_bp)
        return industry_bp_result + platform_bp_result

    # 只导入存续债
    def __queryDebDebentureBase__(self):
        sql = """
                SELECT
                a.id,
                a.issuer_code,
                a.issue_comp_code,
                a.security_id,
                a.secode,
                b.enterprise_name,
                a.deb_name,
                a.short_name,
                a.deb_code,
                a.city_investment,
                a.deb_type,
                a.rate_type,
                a.deb_time,
                a.deb_time_option,
                a.issuing_rate,
                a.coupon_rate,
                a.issuing_method,
                a.`scale`,
                a.remaining_sum,
                a.currency,
                a.market,
                a.starting_interest_date,
                a.expire_date,
                a.underwriters,
                a.deputy_underwriter,
                a.data_download_time,
                a.delete_flag,
                a.next_exercise_date,
                a.next_payment_date,
                a.cashing_type,
                a.remaining_time,
                a.outside_level,
                a.outside_level_value,
                a.perpay_date,
                a.issue_begin_date,
                a.is_continuing,
                a.new_rate,
                a.is_guarantee,
                null as "interest_rate_spread"
            FROM
                regular_income_v2.deb_debenture_base a
                inner join ent_enterprise b on b.credit_code =a.issuer_code 
                where a.is_continuing =1
        """
        return self.localDb.Query(sql)

    def queryAllEsData(self):
        result = []
        after = None
        while True:
            body = {
                "query": {
                    "match_all": {}
                },
                "search_after": after,
                "size": 10000,
                "sort": [
                    {
                        "id": "desc"
                    }
                ]
            }
            if after is None:
                body.pop("search_after")

            queryResult = es.search(index=Consts.es_deb_debenture_base, body=body)
            hits_ = queryResult['hits']['hits']
            if len(hits_) == 0:
                break
            after = queryResult['hits']['hits'][-1]['sort']
            result = result + hits_
        return result

    def toMap(self, data):
        ret = {}
        for item in data:
            ret.update({item['security_id']: {
                'termtomaturity': item['termtomaturity'],
                'interest_rate_spread': item['interest_rate_spread']
            }})
        return ret

    def updateSecuritiesBp(self):
        data = self.__queryBpData()
        base__ = self.queryAllEsData()
        logger.info("es现存债券数据" + str(len(base__)) + "条")
        bpBaseData = self.toMap(data)
        logger.info("查询到利差数据" + str(len(bpBaseData)) + "条")
        actions = []
        for item in base__:
            # 利差
            interest_rate_spread = None
            # 剩余期限
            # termtomaturity = None
            termtomaturity_tag = None
            security_id = item['_source']['security_id']
            remaining_sum = item['_source']['remaining_sum']
            _id = item['_id']
            if remaining_sum is not None:
                if remaining_sum > 5:
                    termtomaturity_tag = '4'
                elif remaining_sum > 3:
                    termtomaturity_tag = '3'
                elif remaining_sum > 1:
                    termtomaturity_tag = '2'
                elif remaining_sum <= 1:
                    termtomaturity_tag = '1'
                else:
                    termtomaturity_tag = None
            if bpBaseData.__contains__(security_id):
                temp = bpBaseData.get(security_id).get('interest_rate_spread')
                # termtomaturity = bpBaseData.get(security_id).get('termtomaturity')
                interest_rate_spread = None if temp is None else temp
                # if termtomaturity is not None:
                #     if termtomaturity > 5:
                #         termtomaturity_tag = '4'
                #     elif termtomaturity > 3:
                #         termtomaturity_tag = '3'
                #     elif termtomaturity > 1:
                #         termtomaturity_tag = '2'
                #     elif termtomaturity <= 1:
                #         termtomaturity_tag = '1'
                #     else:
                #         termtomaturity_tag = None
            if interest_rate_spread is None and termtomaturity_tag is None:
                continue

            index_action = {
                '_op_type': 'update',
                '_index': Consts.es_deb_debenture_base,
                '_type': "test-type",
                "_id": _id,
                'doc': {
                    "interest_rate_spread": interest_rate_spread,
                    "termtomaturity": remaining_sum,
                    "termtomaturity_tag": termtomaturity_tag
                }
            }
            actions.append(index_action)
        logger.info("准备同步数据到ES，条数" + str(len(actions)))
        if actions:
            bulk(es, actions)
        pass

    def sync(self):

        typeByCommand = CommonUtils.isCommandParam(self.arg, "type")

        type = None
        if typeByCommand['exist']:
            type = typeByCommand["value"]

        if type == 'bp':
            self.updateSecuritiesBp()
            return

        base__ = self.__queryDebDebentureBase__()
        if len(base__) > 0:
            es.indices.delete(index=Consts.es_deb_debenture_base)
        es.indices.create(index=Consts.es_deb_debenture_base, ignore=400)
        for item in tqdm(base__, desc="es数据库保存/更新中...", position=0):
            item = dict(item)
            is_including_equity = None
            deb_time_option = item.get("deb_time_option")
            if deb_time_option is not None and deb_time_option.__contains__("N"):
                is_including_equity = 1
            body = {
                "query": {
                    "bool": {
                        "must": [
                            {
                                "term": {
                                    "secode.keyword": item.get("secode")
                                }
                            }
                        ]
                    }
                },
                "track_total_hits": True
            }
            queryResult = es.search(index=Consts.es_deb_debenture_base, body=body)
            if len(queryResult['hits']['hits']) > 0:
                for updateItem in queryResult['hits']['hits']:
                    es.index(index=Consts.es_deb_debenture_base, doc_type='test-type', id=updateItem['_id'],
                             body=item)
            else:
                es.index(index=Consts.es_deb_debenture_base, doc_type='test-type', body=item)


def __main__():
    logger = Logger()
    try:
        t = time.perf_counter()
        localDb = ProdDb(logSql=False, autocommit=True)
        fcdb = FCDB(logSql=False, autocommit=True)
        absSqlServer = AbsSqlServerDev()
        calc = SyncSecuritiesToEs(logger=logger, localDb=localDb, fcdb=fcdb, absSqlServer=absSqlServer)
        # 同步数据
        calc.sync()
        cast = (time.perf_counter() - t)
        m = math.floor(cast / 60)
        s = cast - m * 60
        logger.info('计算结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))
    except Exception as e:
        logger.error("程序发生异常")
        logger.error(e)
        traceback.print_exc()


# 程序入口
if __name__ == "__main__":
    __main__()
